package com.kpmg.datalake.common.database;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.csvreader.CsvWriter;
import com.kpmg.datalake.common.constants.Constants;
import com.kpmg.datalake.common.exception.CustomException;

/**
 * 数据库操作模板类
 * @author Tony
 */
public class DatabaseTemplate {
	
	private DatabaseTemplate(){
		
	}

	private static final Logger logger = LoggerFactory.getLogger(DatabaseTemplate.class);

	/**
	 * 依据SQL查询数据列表
	 * @param sql
	 * @param connection
	 * @return
	 */
	public static List<Map<String, Object>> queryForList(String sql, DataSource dataSource) {
		logger.info("依据SQL查询数据列表:{}",sql);
		List<Map<String, Object>> records = new LinkedList<>();
		ResultSet rs = null;
		try(Connection connection=dataSource.getConnection();
				PreparedStatement stmt = connection.prepareStatement(sql)) {
			rs = stmt.executeQuery();
			queryData(records, rs);
		} catch (Exception e) {
			logger.error("查询列表时出现异常:" + e.getMessage(), e);
			throw new CustomException("查询列表时出现异常:"+e.getMessage());
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
			} catch (Exception e) {
				logger.error("释放资源时出现异常:" + e.getMessage(), e);
			}
		}
		return records;
	}

	private static void queryData(List<Map<String, Object>> records, ResultSet rs) throws SQLException {
		String columnName;
		Object columnData;
		if (null != rs) {
			ResultSetMetaData metaData = rs.getMetaData();
			int count = metaData.getColumnCount();
			if (0 != count) {
				while (rs.next()) {
					Map<String, Object> record = new LinkedHashMap<>();
					for (int i = 0; i < count; i++) {
						columnName = metaData.getColumnName(i + 1).toUpperCase();
						columnData = rs.getObject(i + 1);
						record.put(columnName, columnData == null ? "" : columnData);
					}
					records.add(record);
				}
			}
		}
	}

	/**
	 * 获取目标表数据信息
	 * @param sql
	 * @param connection
	 * @return
	 */
	public static String queryForData(String savePath,String sql, Connection connection) {
		
		logger.info("获取目标表数据信息:{}",sql);
		LocalDateTime start;
		ResultSet rs = null;

		try(BufferedWriter bufferedWriter=new BufferedWriter(new FileWriter(savePath));
			PreparedStatement pstmt = connection.prepareStatement(sql)) {
			CsvWriter writer=new CsvWriter(bufferedWriter, ',');
			String encodingPrefix = new String(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF }, "UTF-8");
			
			logger.info("{}","------------数据库查询开始------------");
			start = LocalDateTime.now();
			rs = pstmt.executeQuery();
			logger.info("数据库查询，耗时：{}ms", Duration.between(start, LocalDateTime.now()).toMillis());
			logger.info("{}", "------------数据库查询结束------------");
			logger.info("{}", "------------文件写入开始------------");
			start = LocalDateTime.now();
			ResultSetMetaData metaData = rs.getMetaData();
			exportData(rs, writer, encodingPrefix, metaData);
			writer.close();
			logger.info("文件写入，耗时：{}ms", Duration.between(start, LocalDateTime.now()).toMillis());
			logger.info("{}","------------文件写入结束------------");
		} catch (Exception e) {
			logger.error("获取目标表数据信息时出现异常:" + e.getMessage(), e);
			throw new CustomException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				logger.error("获取目标表数据信息，释放连接资源时异常:" + e.getMessage(), e);
			}
		}
		
		return savePath;
	}

	private static void exportData(ResultSet rs, CsvWriter writer, String encodingPrefix, ResultSetMetaData metaData)
			throws SQLException, IOException {
		int count = metaData.getColumnCount();
		if (count > 0) {
			StringBuilder header = new StringBuilder();
			for (int i = 0; i < count; i++) {
				header.append(metaData.getColumnLabel(i + 1));
				header.append(Constants.RECORD_SEPARATOR);
			}
			String[] headerArr=header.toString().split(Constants.RECORD_SEPARATOR);
			headerArr[0]=encodingPrefix+headerArr[0];
			writer.writeRecord(headerArr, true);
			
			StringBuilder record=new StringBuilder();
			processResult(rs, writer, count, record);
		}
	}

	private static void processResult(ResultSet rs, CsvWriter writer, int count, StringBuilder record)
			throws SQLException, IOException {
		while (rs.next()) {
			for (int j = 0; j < count; j++) {
				Object object = rs.getObject(j + 1);
				String value = object == null ? "\0" : object.toString();
				if (value.contains(",")&&value.contains("\"")) {
					value = value.replace("\"", "\"\"");
				}
				record.append(value);
				record.append(Constants.RECORD_SEPARATOR);
			}
			writer.writeRecord(record.toString().split(Constants.RECORD_SEPARATOR), true);
			writer.flush();
			record.delete(0, record.length());
		}
	}

	/**
	 * 数据新增
	 * @param tableName
	 * @param param
	 * @param connection
	 */
	public static int insertData(String tableName, Map<String, String> param, DataSource dataSource) {
		PreparedStatement stmt = null;
		int resultInt=0;
		try(Connection connection=dataSource.getConnection()) {
			int columnCnt = 0;
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO ").append(tableName);
			if (null != param && !param.isEmpty()) {
				sql.append("(");
				for (String key : param.keySet()) {
					sql.append(key).append(",");
					columnCnt++;
				}
				sql.setLength(sql.length() - 1);
				sql.append(") VALUES(");
				for (int i = 0; i < columnCnt; i++) {
					sql.append("?,");
				}
				sql.setLength(sql.length() - 1);
				sql.append(")");
				stmt = connection.prepareStatement(sql.toString());
				columnCnt = 0;
				for (String value : param.values()) {
					columnCnt++;
					stmt.setString(columnCnt, value);
				}
				resultInt=stmt.executeUpdate();
			}
		} catch (Exception e1) {
			logger.error("数据新增时出现异常:" + e1.getMessage(), e1);
			throw new CustomException(e1);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e2) {
				logger.error("数据新增，释放连接资源时异常:" + e2.getMessage(), e2);
			}
		}
		return resultInt;
	}
	
	private static final String AND_STR = " AND ";
	/**
	 * 数据更新
	 * @param tableName
	 * @param param
	 * @param condition
	 * @param connection
	 * @return
	 */
	public static int updateData(String tableName, Map<String, String> param,Map<String,String> condition, DataSource dataSource) {
		PreparedStatement pstmt = null;
		int result=0;
		try(Connection connection=dataSource.getConnection()) {
			StringBuilder sqlString = new StringBuilder();
			sqlString.append("UPDATE ").append(tableName);
			if (!param.isEmpty() && !condition.isEmpty()) {
				sqlString.append(" SET ");
				for (Entry<String, String> entry : param.entrySet()) {
					sqlString.append(entry.getKey()).append("='").append(entry.getValue()).append("'").append(",");
				}
				sqlString.setLength(sqlString.length() - 1);
				sqlString.append(" WHERE ");
				for (Entry<String, String> entry : condition.entrySet()) {
					sqlString.append(entry.getKey()).append("='").append(entry.getValue()).append("'").append(AND_STR);
				}
				sqlString.setLength(sqlString.length() - AND_STR.length());
				pstmt = connection.prepareStatement(sqlString.toString());
				result=pstmt.executeUpdate();
			}
		} catch (Exception e3) {
			logger.error("数据更新时出现异常:" + e3.getMessage(), e3);
			throw new CustomException(e3);
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e4) {
				logger.error("数据更新，释放连接资源时异常:" + e4.getMessage(), e4);
			}
		}
		return result;
	}
	
	
	/**
	 * 数据更新
	 * @param sql
	 * @param connection
	 * @return
	 */
	public static int updateData(String sql, DataSource dataSource) {
		PreparedStatement stmt = null;
		int results=0;
		try(Connection connection=dataSource.getConnection()) {
				stmt = connection.prepareStatement(sql);
				results=stmt.executeUpdate();
		} catch (Exception e5) {
			logger.error("数据更新时出现异常:" + e5.getMessage(), e5);
			throw new CustomException(e5);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e6) {
				logger.error("数据更新，释放连接资源时异常:" + e6.getMessage(), e6);
			}
		}
		return results;
	}
	
	/**
	 * 数据删除
	 * @param tableName
	 * @param condition
	 * @param connection
	 * @return
	 */
	public static int deleteData(String tableName,Map<String,String> condition, DataSource dataSource) {
		PreparedStatement stmt = null;
		int result=0;
		try(Connection connection=dataSource.getConnection()) {
			StringBuilder sql = new StringBuilder();
			sql.append("DELETE FROM ").append(tableName);
			if (!condition.isEmpty()) {
				sql.append(" WHERE ");
				for (Entry<String, String> entry : condition.entrySet()) {
					sql.append(entry.getKey()).append("='").append(entry.getValue()).append("'").append(AND_STR);
				}
				sql.setLength(sql.length() - AND_STR.length());
				stmt = connection.prepareStatement(sql.toString());
				result=stmt.executeUpdate();
			}
		} catch (Exception e) {
			logger.error("数据删除时出现异常:" + e.getMessage(), e);
			throw new CustomException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				logger.error("数据删除，释放连接资源时异常:" + e.getMessage(), e);
			}
		}
		return result;
	}
}
